{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>订单</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>757</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>997</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-03-03</td>\n",
       "      <td>956</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-03-04</td>\n",
       "      <td>830</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>122</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期   订单  Unnamed: 2\n",
       "0 2021-03-01  757           9\n",
       "1 2021-03-02  997           9\n",
       "2 2021-03-03  956           9\n",
       "3 2021-03-04  830           9\n",
       "4 2021-03-05  122           9"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 题目一：根据日期和订单字段，要求得出周次（week）、订单总和、日均订单、极大值订单、极小值订单字段\n",
    "# 读取数据集\n",
    "df1 = pd.read_excel('./示例文件.xlsx',sheet_name='data1')\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>订单</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>周次</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>757</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-03-02</td>\n",
       "      <td>997</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-03-03</td>\n",
       "      <td>956</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-03-04</td>\n",
       "      <td>830</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-03-05</td>\n",
       "      <td>122</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-03-06</td>\n",
       "      <td>406</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-03-07</td>\n",
       "      <td>222</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>516</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-03-09</td>\n",
       "      <td>748</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-03-10</td>\n",
       "      <td>229</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>721</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021-03-12</td>\n",
       "      <td>798</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2021-03-13</td>\n",
       "      <td>753</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2021-03-14</td>\n",
       "      <td>363</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>760</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>488</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2021-03-17</td>\n",
       "      <td>513</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2021-03-18</td>\n",
       "      <td>819</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2021-03-19</td>\n",
       "      <td>442</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2021-03-20</td>\n",
       "      <td>948</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2021-03-21</td>\n",
       "      <td>192</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2021-03-22</td>\n",
       "      <td>164</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2021-03-23</td>\n",
       "      <td>474</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2021-03-24</td>\n",
       "      <td>553</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2021-03-25</td>\n",
       "      <td>493</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2021-03-26</td>\n",
       "      <td>216</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2021-03-27</td>\n",
       "      <td>647</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2021-03-28</td>\n",
       "      <td>871</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2021-03-29</td>\n",
       "      <td>340</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2021-03-30</td>\n",
       "      <td>135</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2021-03-31</td>\n",
       "      <td>789</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期   订单  Unnamed: 2  周次\n",
       "0  2021-03-01  757           9   9\n",
       "1  2021-03-02  997           9   9\n",
       "2  2021-03-03  956           9   9\n",
       "3  2021-03-04  830           9   9\n",
       "4  2021-03-05  122           9   9\n",
       "5  2021-03-06  406           9   9\n",
       "6  2021-03-07  222           9   9\n",
       "7  2021-03-08  516          10  10\n",
       "8  2021-03-09  748          10  10\n",
       "9  2021-03-10  229          10  10\n",
       "10 2021-03-11  721          10  10\n",
       "11 2021-03-12  798          10  10\n",
       "12 2021-03-13  753          10  10\n",
       "13 2021-03-14  363          10  10\n",
       "14 2021-03-15  760          11  11\n",
       "15 2021-03-16  488          11  11\n",
       "16 2021-03-17  513          11  11\n",
       "17 2021-03-18  819          11  11\n",
       "18 2021-03-19  442          11  11\n",
       "19 2021-03-20  948          11  11\n",
       "20 2021-03-21  192          11  11\n",
       "21 2021-03-22  164          12  12\n",
       "22 2021-03-23  474          12  12\n",
       "23 2021-03-24  553          12  12\n",
       "24 2021-03-25  493          12  12\n",
       "25 2021-03-26  216          12  12\n",
       "26 2021-03-27  647          12  12\n",
       "27 2021-03-28  871          12  12\n",
       "28 2021-03-29  340          13  13\n",
       "29 2021-03-30  135          13  13\n",
       "30 2021-03-31  789          13  13"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['周次'] = df1['日期'].dt.isocalendar().week.map(str)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>周次</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>4128</td>\n",
       "      <td>590.0</td>\n",
       "      <td>798</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>4162</td>\n",
       "      <td>595.0</td>\n",
       "      <td>948</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>3418</td>\n",
       "      <td>488.0</td>\n",
       "      <td>871</td>\n",
       "      <td>164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>1264</td>\n",
       "      <td>421.0</td>\n",
       "      <td>789</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>4290</td>\n",
       "      <td>613.0</td>\n",
       "      <td>997</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   周次   sum   mean  max  min\n",
       "0  10  4128  590.0  798  229\n",
       "1  11  4162  595.0  948  192\n",
       "2  12  3418  488.0  871  164\n",
       "3  13  1264  421.0  789  135\n",
       "4   9  4290  613.0  997  122"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_result = df1.groupby(['周次'])['订单'].agg(['sum', 'mean', 'max','min']).round(0).reset_index().sort_values(by=['周次'])\n",
    "df1_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>科目</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>语文</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>语文</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>数学</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>数学</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>外语</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  姓名  科目  分数\n",
       "0  A  语文  65\n",
       "1  B  语文  86\n",
       "2  A  数学  94\n",
       "3  B  数学  63\n",
       "4  A  外语  67"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 题目二：行转列并求每个人的平均分和总分\n",
    "# 读取数据集\n",
    "df2 = pd.read_excel('./示例文件.xlsx',sheet_name='data2')\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\software\\anaconda\\lib\\site-packages\\pandas\\core\\reshape\\merge.py:648: UserWarning: merging between different levels can give an unintended result (3 levels on the left,2 on the right)\n",
      "  warnings.warn(msg, UserWarning)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>(sum, 分数, 外语)</th>\n",
       "      <th>(sum, 分数, 数学)</th>\n",
       "      <th>(sum, 分数, 语文)</th>\n",
       "      <th>(sum, 分数, 总分)</th>\n",
       "      <th>(mean, 分数)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>67</td>\n",
       "      <td>94</td>\n",
       "      <td>65</td>\n",
       "      <td>226</td>\n",
       "      <td>75.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>72</td>\n",
       "      <td>63</td>\n",
       "      <td>86</td>\n",
       "      <td>221</td>\n",
       "      <td>73.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>总分</td>\n",
       "      <td>139</td>\n",
       "      <td>157</td>\n",
       "      <td>151</td>\n",
       "      <td>447</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名  (sum, 分数, 外语)  (sum, 分数, 数学)  (sum, 分数, 语文)  (sum, 分数, 总分)  (mean, 分数)\n",
       "0   A             67             94             65            226   75.333333\n",
       "1   B             72             63             86            221   73.666667\n",
       "2  总分            139            157            151            447         NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2_pv1 = pd.pivot_table(df2,index=['姓名'],columns=['科目'],values=['分数'],aggfunc=[np.sum],margins=True,margins_name='总分',fill_value=0)\n",
    "df2_pv2 = pd.pivot_table(df2,index=['姓名'],values=['分数'],aggfunc=[np.mean],fill_value=0)\n",
    "df2_result = pd.merge(df2_pv1,df2_pv2,on='姓名',how='left').reset_index()\n",
    "df2_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省</th>\n",
       "      <th>市</th>\n",
       "      <th>区</th>\n",
       "      <th>店铺名称</th>\n",
       "      <th>订单数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>2033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>1729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>4030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>D</td>\n",
       "      <td>2648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>2714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   省  市  区 店铺名称   订单数\n",
       "0  A  A  A    A  2033\n",
       "1  A  A  B    B  1729\n",
       "2  B  D  D    C  4030\n",
       "3  B  D  E    D  2648\n",
       "4  B  D  D    E  2714"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 题目三：按照 区 排序，看到每一个业务区内订单排名前 3 名的门店及订单数\n",
    "# 读取数据集\n",
    "df3 = pd.read_excel('./示例文件.xlsx',sheet_name='data3')\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>区</th>\n",
       "      <th>店铺名称</th>\n",
       "      <th>订单数</th>\n",
       "      <th>组内降序排名</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>2033</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>B</td>\n",
       "      <td>1729</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>F</td>\n",
       "      <td>1950</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>C</td>\n",
       "      <td>4030</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>2714</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>E</td>\n",
       "      <td>D</td>\n",
       "      <td>2648</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>E</td>\n",
       "      <td>G</td>\n",
       "      <td>2233</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>F</td>\n",
       "      <td>H</td>\n",
       "      <td>4025</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   区 店铺名称   订单数  组内降序排名\n",
       "0  A    A  2033       1\n",
       "1  B    B  1729       1\n",
       "2  C    F  1950       1\n",
       "3  D    C  4030       1\n",
       "4  D    E  2714       2\n",
       "5  E    D  2648       1\n",
       "6  E    G  2233       2\n",
       "7  F    H  4025       1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3_group = df3.groupby(['区','店铺名称'])['订单数'].sum().reset_index()\n",
    "df3_group['组内降序排名'] = df3.groupby(['区'])['订单数'].rank(method='dense',ascending=False).astype(int)\n",
    "df3_result = df3_group.query('组内降序排名 <= 3')\n",
    "df3_result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
